CREATE procedure sp_asi_UpdateEventFR
as
set nocount on
declare
@MeetCode varchar(10),
@Appeal varchar(40),
@Campaign varchar(10),
@Fund varchar(5),
@OrderNumber float,
@FairMarketValue money,
@ProductCode varchar(31),
@ProdTitle varchar(60),
@RefNum int,
@Status varchar(1),
@ActivityType varchar(10),
@Seqn int,
@Id varchar(10),
@TransDate datetime,
@TransNumber int,
@Result int,
@NewSeqn int,
@Amount money,
@SourceCode varchar(40)
declare Get_FREvents cursor for
select
max(m.MEET_APPEAL) MEET_APPEAL,
max(m.MEET_CAMPAIGN) MEET_CAMPAIGN,
max(m.ORG_CODE) ORG_CODE,
max(m.STATUS) STATUS,
max(p.TAXABLE_VALUE) TAXABLE_VALUE,
p.PRODUCT_CODE,
max(p.TITLE) TITLE,
max(om.ORDER_NUMBER) ORDER_NUMBER,
max(o.INVOICE_REFERENCE_NUM) INVOICE_REFERENCE_NUM,
max(ol.EXTENDED_AMOUNT) EXTENDED_AMOUNT,
max(t.ACTIVITY_TYPE) ACTIVITY_TYPE,
max(t.ACTIVITY_SEQN) ACTIVITY_SEQN,
t.ST_ID,
max(t.TRANSACTION_DATE) TRANSACTION_DATE,
max(t.TRANS_NUMBER) TRANS_NUMBER,
max(t.SOURCE_CODE) SOURCE_CODE
from Meet_Master m
join Order_Meet om on m.MEETING=om.MEETING
join Orders o on om.ORDER_NUMBER=o.ORDER_NUMBER
join Order_Lines ol on ol.ORDER_NUMBER=o.ORDER_NUMBER
join Product p on p.PRODUCT_MAJOR=m.MEETING
join Trans t on t.INVOICE_REFERENCE_NUM=o.INVOICE_REFERENCE_NUM
where m.IS_FR_MEET=1 and t.TRANSACTION_TYPE='DIST' and t.JOURNAL_TYPE='IN'
and t.SOURCE_SYSTEM='MEETING' and ol.PRODUCT_CODE=p.PRODUCT_CODE and ol.EXTENDED_AMOUNT>0
group by p.PRODUCT_CODE,t.ST_ID
open Get_FREvents
fetch next from Get_FREvents into @Appeal,@Campaign,@Fund,@Status,@FairMarketValue,@ProductCode,@ProdTitle,
@OrderNumber,@RefNum,@Amount,@ActivityType,@Seqn,@Id,@TransDate,@TransNumber,@SourceCode
WHILE @@FETCH_STATUS = 0
BEGIN
if NOT EXISTS(select * from Activity where ID=@Id and PRODUCT_CODE=@ProductCode and ACTIVITY_TYPE='GIFT')
BEGIN
If @Status<>'C'
begin
update Order_Lines set MEET_APPEAL=@Appeal,MEET_CAMPAIGN=@Campaign,ORG_CODE=@Fund,
FAIR_MARKET_VALUE=@FairMarketValue,IS_FR_ITEM=1
where PRODUCT_CODE=@ProductCode
end
update Product set APPEAL_CODE=@Appeal,CAMPAIGN_CODE=@Campaign,ORG_CODE=@Fund,
IS_FR_ITEM=1 where PRODUCT_CODE=@ProductCode
update Product_Function set IS_FR_ITEM=1 where PRODUCT_CODE=@ProductCode
if isnull(@Appeal,'')='' and isnull(@SourceCode,'')<>''
begin
select @Appeal=@SourceCode
end
update Trans set SOURCE_CODE=@Appeal,CAMPAIGN_CODE=@Campaign,
IS_FR_ITEM=1, ACTIVITY_TYPE=CASE ACTIVITY_TYPE WHEN 'PLEDGE' THEN 'GIFT' ELSE ACTIVITY_TYPE END
where INVOICE_REFERENCE_NUM=@RefNum and TRANSACTION_TYPE='DIST'
exec @Result=sp_iboGetCounter 'Activity',1,@NewSeqn output
insert into Activity(SEQN,ID,ACTIVITY_TYPE,PRODUCT_CODE,OTHER_CODE,TRANSACTION_DATE,DESCRIPTION,
EFFECTIVE_DATE,AMOUNT,SOURCE_CODE,SOURCE_SYSTEM,CO_ID,MEMBER_TYPE,ORIGINATING_TRANS_NUM,
ORG_CODE,CAMPAIGN_CODE,OTHER_ID,TAXABLE_VALUE)
select @NewSeqn,@Id,'GIFT',@ProductCode,'GIFT',@TransDate,@ProdTitle,
NULL,@Amount,@Appeal,'MEETING',
(CASE n.COMPANY_RECORD WHEN 1 then n.ID WHEN 0 then n.CO_ID END),
n.MEMBER_TYPE,@TransNumber,@Fund,@Campaign,@Id,@FairMarketValue
from Name n where n.ID =@Id
END
fetch next from Get_FREvents into @Appeal,@Campaign,@Fund,@Status,@FairMarketValue,@ProductCode,@ProdTitle,
@OrderNumber,@RefNum,@Amount,@ActivityType,@Seqn,@Id,@TransDate,@TransNumber,@SourceCode
END
close Get_FREvents
deallocate Get_FREvents
update Trans set Trans.SOURCE_CODE=a.SOURCE_CODE,Trans.CAMPAIGN_CODE=a.CAMPAIGN_CODE,Trans.IS_FR_ITEM=1
from Trans
join Activity a on a.ORIGINATING_TRANS_NUM=Trans.TRANS_NUMBER
join Invoice i on i.ORIGINATING_TRANS_NUM=a.ORIGINATING_TRANS_NUM
where Trans.TRANSACTION_TYPE='DIST' and Trans.JOURNAL_TYPE='IN' and Trans.SOURCE_SYSTEM='MEETING'
and Trans.IS_FR_ITEM=0 and Trans.INVOICE_REFERENCE_NUM=i.REFERENCE_NUM
and a.ACTIVITY_TYPE in ('GIFT','PLEDGE')
update Trans set INSTALL_BILL_DATE=NULL where SOURCE_SYSTEM='MEETING'
update Invoice set INSTALL_BILL_DATE=NULL where SOURCE_SYSTEM='MEETING'
update Activity set ACTIVITY_TYPE='GIFT' where ACTIVITY_TYPE='PLEDGE'
and SOURCE_SYSTEM='MEETING'

GO
GRANT EXECUTE ON  [dbo].[sp_asi_UpdateEventFR] TO [IMIS]
GO